﻿using System;
using System.Collections.Generic;
using System.Data;
using Pku.BookStore.Model;

namespace Pku.BookStore.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class EmployeeDAL : BaseDAL
    {
        string commandText = string.Empty;

        /// <summary>
        /// 增加员工信息
        /// </summary>
        /// <param name="entity"></param>
        public int Add(EmployeeModel entity)
        {
            commandText = @"INSERT INTO Employee(EmployeeName,EmployeePass,LastDate,LastIP)
VALUES(,@EmployeeName,@EmployeePass,getdate(),@LastIP);
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "EmployeeName", DbType.String, entity.EmployeeName);
            DbMain.AddInParameter(command, "EmployeePass", DbType.AnsiString, entity.EmployeePass);
            DbMain.AddInParameter(command, "LastIP", DbType.AnsiString, entity.LastIP);

            return (int)DbMain.ExecuteNonQuery(command);

        }

        /// <summary>
        /// 修改员工信息
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(EmployeeModel entity)
        {
            commandText = @"UPDATE Employee SET EmployeeID=@EmployeeID,EmployeeName=@EmployeeName,EmployeePass=@EmployeePass,LastDate=@LastDate,LastIP=@LastIP WHERE EmployeeID=@EmployeeID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "EmployeeID", DbType.Int32, entity.EmployeeID);
            DbMain.AddInParameter(command, "EmployeeName", DbType.String, entity.EmployeeName);
            DbMain.AddInParameter(command, "EmployeePass", DbType.AnsiString, entity.EmployeePass);
            DbMain.AddInParameter(command, "LastDate", DbType.DateTime, entity.LastDate);
            DbMain.AddInParameter(command, "LastIP", DbType.AnsiString, entity.LastIP);

            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 搜索
        /// </summary>
        /// <returns></returns>
        public List<EmployeeModel> Search(EmployeeParam param)
        {
            List<EmployeeModel> list = new List<EmployeeModel>();
            commandText = @"SELECT EmployeeID,EmployeeName,EmployeePass,LastDate,LastIP  FROM  Employee";
            using (var dr = DbMain.ExecuteReader(CommandType.Text, commandText))
            {
                while (dr.Read())
                {
                    list.Add(ConvertReaderToEntity(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 获取员工实体
        /// </summary>
        /// <param name="employeeID"></param>
        /// <returns></returns>
        public EmployeeModel GetEntityById(int employeeID)
        {
            commandText = @"SELECT EmployeeID,EmployeeName,EmployeePass,LastDate,LastIP  FROM  Employee WHERE EmployeeID=@EmployeeID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "EmployeeID", DbType.Int32, employeeID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }

        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static EmployeeModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new EmployeeModel
            {
                EmployeeID = (int)dr["EmployeeID"],
                EmployeeName = dr["EmployeeName"].ToString(),
                EmployeePass = dr["EmployeePass"].ToString(),
                LastDate = Convert.ToDateTime(dr["LastDate"]),
                LastIP = dr["LastIP"].ToString()
            };
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <returns></returns>
        public bool Exist(string Str)
        {
            commandText = "SELECT COUNT(1) FROM Employee e (NOLOCK) WHERE e.EmployeeName=@EmployeeName";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "EmployeeName", DbType.String, Str);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public EmployeeModel Login(EmployeeModel entity)
        {
            commandText = "SELECT EmployeeID,EmployeeName,EmployeePass,LastDate,LastIP FROM Employee e(NOLOCK) WHERE e.EmployeeName=@EmployeeName AND e.EmployeePass=@EmployeePass";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "EmployeeName", DbType.String, entity.EmployeeName);
            DbMain.AddInParameter(command, "EmployeePass", DbType.String, entity.EmployeePass);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }

        /// <summary>
        /// 修改最后登录信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool ModifyLast(EmployeeModel entity)
        {
            commandText = "UPDATE Employee SET LastDate = 'GetDate()', LastIP = @LastIP WHERE EmployeeID=@EmployeeID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "LastIP", DbType.String, entity.LastIP);
            DbMain.AddInParameter(command, "EmployeeID", DbType.Int32, entity.EmployeeID);
            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 修改员工密码
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool ModifyPass(EmployeeModel entity)
        {
            commandText = "UPDATE Employee SET EmployeePass = @EmployeePass WHERE EmployeeID=@EmployeeID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "EmployeePass", DbType.String, entity.EmployeePass);
            DbMain.AddInParameter(command, "EmployeeID", DbType.Int32, entity.EmployeeID);
            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

    }

}